<?php 
/* 	These code was generated using phpCIGen v 2.a (21/05/2012)
	#zaqi 		zaqi.smart@gmail.com,http://zenzaqi.blogspot.com,
    #CV. Trust Solution, jl. Saronojiwo 2/19 Surabaya, http://www.ts.co.id

	+ Module  		: t_kontrak Model
	+ Description	: For record model process back-end
	+ Filename 		: c_t_kontrak.php
 	+ creator 		: 
 	+ Created on 24/Jul/2012 10:52:52

*/

class M_t_kontrak extends CI_Model{

		//constructor
		function __construct() {
			parent::__construct();
		}
		function get_komp_byfilter($branch, $project, $job,$vendor){
			
			$query = "SELECT div_name FROM t_rapk 
						LEFT JOIN m_div ON
						m_div_id = div_id
						WHERE 
						m_branch_id = ".$branch
						." AND t_project_id = ". $project 
						." AND m_job_id = ".$job;
			$row = $this->db->query($query);
			$divisi = $row->row('div_name');
	//		echo $divisi;
			// $query = "SELECT m_div_id FROM m_job WHERE job_id = ".$job;
			// $row = $this->db->query($query);
			// $divisi = $row->row('m_div_id');
			
			if ( $divisi != '503. Sub Kontraktor'){
			$query = "SELECT komp_id, vendor_id, vendor_name, vendor_address, komp_date, komp_no,jobdet_id, jobdet_name, kompdet_vol, unit_id, unit_name, kompdet_price
					FROM t_komp JOIN t_kompdet ON(t_komp.komp_id = t_kompdet.t_komp_id)
					LEFT JOIN t_spkdet ON(t_spkdet.t_kompdet_id = t_kompdet.kompdet_id)
					LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
					LEFT JOIN m_jobdet ON(t_kompdet.m_jobdet_id = m_jobdet.jobdet_id)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					LEFT JOIN m_job ON (m_job.job_id = t_komp.m_job_id)
					WHERE t_komp.m_branch_id = ".$branch." AND t_komp.t_project_id = ".$project." AND t_komp.m_job_id = ".$job."
					AND t_komp.m_vendor_id = '" . $vendor . "'";
			}else{
			$query = "
				SELECT
					komp_id,
					vendor_id,
					vendor_name,
					vendor_address,
					komp_date,
					komp_no,
					jobdet_id,
					node.kompdet_vol,
					unit_id,
					unit_name,
					node.kompdet_price,
					node.kompdet_id AS kompdet_id,
					jobdet_id,
					IFNULL(
						CONCAT(

							REPEAT
								(
									'--',
									COUNT(parent.kompdet_id)- 1
								),
								jobdet_name
						),
						node.kompdet_uraian
					)AS jobdet_name,
					(COUNT(parent.kompdet_id) - 1)AS depth
				FROM
					t_kompdet as node
				Join t_kompdet as parent ON(
					node.kompdet_lft BETWEEN parent.kompdet_lft
					AND parent.kompdet_rgt
				)
				LEFT JOIN t_spkdet ON(
					t_spkdet.t_kompdet_id = node.kompdet_id
				)
				LEFT JOIN t_komp ON(
					node.t_komp_id = t_komp.komp_id
				)
				LEFT JOIN m_vendor ON(
					m_vendor.vendor_id = t_komp.m_vendor_id
				)
				LEFT JOIN m_job ON(
					m_job.job_id = t_komp.m_job_id
				)
				LEFT JOIN m_jobdet ON(
					m_jobdet.jobdet_id = node.m_jobdet_id
				)
				LEFT JOIN m_unit ON(
					m_unit.unit_id = node.kompdet_unit
				)
				WHERE t_komp.m_branch_id = ".$branch." AND t_komp.t_project_id = ".$project." AND t_komp.m_job_id = ".$job."
				AND t_komp.m_vendor_id = '" . $vendor . "'
				GROUP BY
					node.kompdet_id
				ORDER BY
					node.kompdet_lft
				"; 
			}
			
			/* $query = "SELECT komp_id, vendor_id, vendor_name, vendor_address, komp_date, komp_no,jobdet_id, jobdet_name, kompdet_vol, unit_id, unit_name, kompdet_price
				FROM t_kompdet 
				LEFT JOIN t_komp ON(t_kompdet.t_komp_id = t_komp.komp_id)
				LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
				LEFT JOIN m_job ON (m_job.job_id = t_komp.m_job_id)
				LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_kompdet.m_jobdet_id)
				LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
				WHERE t_komp.m_branch_id = ".$branch." AND t_komp.t_project_id = ".$project." AND t_komp.m_job_id = ".$job."
				AND t_komp.m_vendor_id = '" . $vendor . "'
				"; */
			$this->firephp->log($query);
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
				
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function get_kontrak($spk_id){
			$sql = "SELECT kontrak_no, kontrak_date, kontrak_begin, kontrak_end, project_id, project_name, project_value, project_pm,
						vendor_name, vendor_address,vendor_dir, vendor_dirpos,vendor_cert, vendor_certdate, vendor_bank, vendor_rek, vendor_bankowner,
						job_id, job_name,t_rapk.m_div_id, komp_no, komp_date, nego_date
					FROM t_kontrak JOIN t_spk ON(t_spk.spk_id = t_kontrak.t_spk_id)
					JOIN t_komp ON(t_komp.komp_id = t_spk.t_komp_id)
					LEFT JOIN t_project ON(t_project.project_id = t_komp.t_project_id)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					LEFT JOIN m_job ON(m_job.job_id = t_komp.m_job_id)
					LEFT JOIN t_nego ON(t_nego.nego_id = t_spk.t_nego_id)
					LEFT JOIN t_rapk ON t_komp.m_branch_id =  t_komp.m_branch_id AND  t_komp.m_job_id = t_rapk.m_job_id AND  t_komp.t_project_id =  t_rapk.t_project_id
					WHERE t_spk.spk_id = ".$spk_id;
			$rs = $this->db->query($sql);
			if($rs->num_rows()){
				return $rs->row();
			}else{
				return null;
			}
		}
		
		function get_badet($ba_id){
			$sql = "SELECT
				jobdet_name, kompdet_vol AS volume, unit_name, kompdet_price AS harga, (kompdet_vol * kompdet_price) AS subtotal
				FROM t_kompdet
				JOIN t_komp ON t_komp.komp_id = t_kompdet.t_komp_id
				JOIN m_unit ON t_kompdet.kompdet_unit = m_unit.unit_id
				JOIN t_spk ON t_spk.t_komp_id = t_komp.komp_id
				JOIN m_jobdet ON t_kompdet.m_jobdet_id = m_jobdet.jobdet_id
				WHERE spk_id = ".$ba_id;
			$rs = $this->db->query($sql);
			if($rs->num_rows()){
				return $rs->result();
			}else{
				return null;
			}
		}
		
		function get_pasal($kontrak_no){
			$sql = "SELECT tpasal_id, tpasal_pasal_seq, tpasal_pasal_no, tpasal_pasal_title, tpasal_pasal_content FROM t_pasal WHERE tpasal_kontrak_no = '" . $kontrak_no . "' ORDER BY tpasal_pasal_seq ASC";
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
			
			if($nbrows>0){
				/*$content = "";
				$record = $result->row();
				$content = $record->pasal_content;
				$content = preg_replace("/{VENDOR_PEMENANG}/", 'Nama Vendor Pemenang', $content);
				return $content;*/
				return $result->result();
			} else {
				return 'error';
			}
		}
		
		function badet_list($branch,$project,$job) {
			// $sql = "SELECT badet_id, jobdet_id, jobdet_name, kompdet_vol, unit_id, unit_name, kompdet_price
					// FROM t_badet JOIN t_ba ON(t_ba.ba_id = t_badet.t_ba_id) 
					// JOIN t_nego ON(t_nego.nego_id = t_ba.t_nego_id)
					// JOIN t_kompdet ON(t_kompdet.kompdet_id = t_badet.t_kompdet_id)
					// LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_kompdet.m_jobdet_id)
					// LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
					// WHERE t_nego.m_branch_id = ".$branch." AND t_nego.t_project_id = ".$project." AND t_nego.m_job_id = ".$job;
			$sql = "SELECT badet_id, jobdet_id, jobdet_name, kompdet_vol, unit_id, unit_name, kompdet_price
			FROM t_spkdet JOIN t_spk ON(t_spk.spk_id = t_spkdet.t_spk_id) 
			JOIN t_nego ON(t_nego.nego_id = t_spk.t_nego_id)
			JOIN t_kompdet ON(t_kompdet.kompdet_id = t_spkdet.t_kompdet_id)
			LEFT JOIN m_jobdet ON(m_jobdet.jobdet_id = t_kompdet.m_jobdet_id)
			LEFT JOIN m_unit ON(m_unit.unit_id = t_kompdet.kompdet_unit)
			WHERE t_nego.m_branch_id = ".$branch." AND t_nego.t_project_id = ".$project." AND t_nego.m_job_id = ".$job;
//			$this->firephp->log($sql);
//			echo $sql;
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
				
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function get_nokontrak($nokontrak) {
			if ($nokontrak==""){
				return 0;
			}
			
			$query = "SELECT kontrak_no
					FROM t_kontrak
					WHERE kontrak_no='".$nokontrak."'";
			
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			
			if($nbrows>0){
				return 0;
			} else {
				return 1;
			}
		}
		
		function get_jobdet_all($filter, $start, $end){
			$query = "SELECT IFNULL(rapkdet_id, 0) AS rapkdet_id, jobdet_id, jobdet_name
				FROM m_jobdet LEFT JOIN t_rapkdet ON(t_rapkdet.m_jobdet_id = m_jobdet.jobdet_id)";
		
			// For simple search
			if ($filter<>""){
				$query .=preg_match("/\bWHERE\b/i",$query)? " AND ":" WHERE ";
				$query .= " (jobdet_name LIKE '%".addslashes($filter)."%' )";
			}
				
			$result = $this->db->query($query);
			$nbrows = $result->num_rows();
			$limit = $query." LIMIT ".$start.", ".$end;
			$result = $this->db->query($limit);
				
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function get_pasal_all($kontrak_no) {
			/* $sql = "SELECT tpasal_id, tpasal_pasal_seq, tpasal_pasal_no, tpasal_pasal_title, tpasal_pasal_content, CONCAT(SUBSTRING_INDEX(pasal_content,' ',50),'...') AS pasal_content_partial
					FROM m_pasal
					WHERE tpasal_kontrak_no = '". $kontrak_no ."'
					ORDER BY tpasal_pasal_seq ASC"; */
			$sql = "SELECT tpasal_id as pasal_id, tpasal_pasal_seq as pasal_seq, tpasal_pasal_no as pasal_no, tpasal_pasal_title as pasal_title,
					tpasal_pasal_content as pasal_content, CONCAT(SUBSTRING_INDEX(tpasal_pasal_content,' ',50),'...') AS pasal_content_partial
					FROM t_pasal
					WHERE tpasal_kontrak_no = '". $kontrak_no ."'
					ORDER BY tpasal_pasal_seq ASC";
				
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
		
			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}
		
		function pasal_edit_save($pasal_id, $pasal_content){
			$data = array(
				"tpasal_pasal_content"=>$pasal_content
			);
			$this->db->where('tpasal_id', $pasal_id);
			$this->db->update('t_pasal', $data);
			if($this->db->affected_rows()){
				return '1';
			}else {
				return '0';
			}
			
		}

		//function for get list record
		function t_kontrak_list($filter,$start,$end){
			$sql = "SELECT spk_id, branch_id, branch_name, project_id, project_code, project_name, job_id, job_name,t_rapk.m_div_id, vendor_id, vendor_name, vendor_address, 
					IFNULL(kontrak_id,0) as kontrak_id, kontrak_no, kontrak_date, kontrak_begin, kontrak_end
					FROM t_spk JOIN t_komp ON(t_komp.komp_id = t_spk.t_komp_id)
					LEFT JOIN m_branch ON(m_branch.branch_id = t_komp.m_branch_id)
					LEFT JOIN t_project ON(t_project.project_id = t_komp.t_project_id)
					LEFT JOIN m_job ON(m_job.job_id = t_komp.m_job_id)
					LEFT JOIN m_vendor ON(m_vendor.vendor_id = t_komp.m_vendor_id)
					LEFT JOIN t_kontrak ON(t_kontrak.t_spk_id = t_spk.spk_id)
					LEFT JOIN t_rapk ON t_komp.m_branch_id =  t_komp.m_branch_id AND  t_komp.m_job_id = t_rapk.m_job_id AND  t_komp.t_project_id =  t_rapk.t_project_id
					WHERE t_spk.t_nego_id <> 0  
				";
			// For simple search
			if ($filter<>""){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (project_code LIKE '%".addslashes($filter)."%' OR
					project_name LIKE '%".addslashes($filter)."%' OR
					job_name LIKE '%".addslashes($filter)."%' OR
					kontrak_no LIKE '%".addslashes($filter)."%'
					)";
			}

			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
			$limit = $sql." LIMIT ".$start.", ".$end;
			$result = $this->db->query($limit);

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}

		//function for create new record
		function t_kontrak_cu($kontrak_id, $t_spk_id, $kontrak_date, $kontrak_no, $kontrak_begin, $kontrak_end,$kontrak_type){
			if($kontrak_id == 0){
				//add
				$data = array(
						"t_spk_id"=>$t_spk_id,
						"kontrak_date"=>$kontrak_date,
						"kontrak_no"=>$kontrak_no,
						"kontrak_begin"=>$kontrak_begin,
						"kontrak_end"=>$kontrak_end
						);
				$this->db->insert('t_kontrak', $data);
				if($this->db->affected_rows()){
					$this->insert_tpasal($kontrak_type, $kontrak_no);
					return '1';
				}else {
					return '0';
				}
			}else{
				return '1';
			}
			
		}

		//function for update record
		function t_kontrak_update($kontrak_id, $branch, $project, $job, $vendor, $kontrak_date, $kontrak_no, 
				$array_rapkdet_id, $array_kontrakdet_id, $array_jobdet_id, $array_kontrakdet_vol, $array_kontrakdet_unit, 
				$array_kontrakdet_rapkprice, $array_kontrakdet_price){
			for($i = 0; $i < sizeof($array_jobdet_id); $i++){
				$kontrakdet_id = $array_kontrakdet_id[$i];
				$t_rapkdet_id = $array_rapkdet_id[$i];
				$m_jobdet_id = $array_jobdet_id[$i];
				$kontrakdet_unit = $array_kontrakdet_unit[$i];
				$kontrakdet_vol = $array_kontrakdet_vol[$i];
				$kontrakdet_rapkprice = $array_kontrakdet_rapkprice[$i];
				$kontrakdet_price = $array_kontrakdet_price[$i];
			
				if($kontrakdet_id == 0){
					//insert data
					$data_c = array(
							"t_kontrak_id"=>$kontrak_id,
							"t_rapkdet_id"=>$t_rapkdet_id,
							"m_jobdet_id"=>$m_jobdet_id,
							"kontrakdet_unit"=>$kontrakdet_unit,
							"kontrakdet_vol"=>$kontrakdet_vol,
							"kontrakdet_rapkprice"=>$kontrakdet_rapkprice,
							"kontrakdet_price"=>$kontrakdet_price
							);
					$this->db->insert('t_kontrakdet', $data_c);
				}else{
					$data_u = array(
							"kontrakdet_unit"=>$kontrakdet_unit,
							"kontrakdet_vol"=>$kontrakdet_vol,
							"kontrakdet_rapkprice"=>$kontrakdet_rapkprice,
							"kontrakdet_price"=>$kontrakdet_price
							);
					$this->db->where('kontrakdet_id', $kontrakdet_id);
					$this->db->update('t_kontrakdet', $data_u);
				}
					
			}
				
			return '1';
		}

		//fcuntion for delete record
		function t_kontrak_delete($pkid){
			// You could do some checkups here and return '0' or other error consts.
			// Make a single query to delete all of the t_kontraks at the same time :
			if(sizeof($pkid)<1){
				return '0';
			} else if (sizeof($pkid) == 1){
				$sql = "DELETE FROM t_kontrak WHERE kontrak_id,m_branch_id = ".$pkid[0];
				$this->db->query($sql);
			} else {
				$sql = "DELETE FROM t_kontrak WHERE ";
				for($i = 0; $i < sizeof($pkid); $i++){
					$sql = $sql . "kontrak_id,m_branch_id= ".$pkid[$i];
					if($i<sizeof($pkid)-1){
						$sql = $sql . " OR ";
					}
				}
				$this->db->query($sql);
			}
			if($this->db->affected_rows()>0)
				return '1';
			else
				return '0';
		}

		//function for advanced search record
		function t_kontrak_search($kontrak_id,
				 $kontrak_flag,
				 $kontrak_code,
				 $kontrak_type,
				 $kontrak_name,
				 $kontrak_shortname,
				 $kontrak_pm,
				 $kontrak_semcm,
				 $kontrak_value,
				 $kontrak_start,
				 $kontrak_p1,
				 $kontrak_p2,
				 $kontrak_created_date,
				 $kontrak_created_by,
				 $kontrak_updated_date,
				 $kontrak_updated_by,
				 $kontrak_updated_count,
				 $m_branch,$start,$end){
			//full query
			$sql = "SELECT kontrak_id,
						   kontrak_flag,
						   kontrak_code,
						   kontrak_type,
						   kontrak_name,
						   kontrak_shortname,
						   kontrak_pm,
						   kontrak_semcm,
						   kontrak_value,
						   kontrak_start,
						   kontrak_p1,
						   kontrak_p2,
						   kontrak_created_date,
						   kontrak_created_by,
						   kontrak_updated_date,
						   kontrak_updated_by,
						   kontrak_updated_count,
						   m_branch_id
					FROM   t_kontrak";

			if($kontrak_id!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_id) LIKE '%".addslashes(strtolower($kontrak_id))."%'";
			};
			if($kontrak_flag!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_flag) LIKE '%".addslashes(strtolower($kontrak_flag))."%'";
			};
			if($kontrak_code!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_code) LIKE '%".addslashes(strtolower($kontrak_code))."%'";
			};
			if($kontrak_type!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_type) LIKE '%".addslashes(strtolower($kontrak_type))."%'";
			};
			if($kontrak_name!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_name) LIKE '%".addslashes(strtolower($kontrak_name))."%'";
			};
			if($kontrak_shortname!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_shortname) LIKE '%".addslashes(strtolower($kontrak_shortname))."%'";
			};
			if($kontrak_pm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_pm) LIKE '%".addslashes(strtolower($kontrak_pm))."%'";
			};
			if($kontrak_semcm!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_semcm) LIKE '%".addslashes(strtolower($kontrak_semcm))."%'";
			};
			if($kontrak_value!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_value) LIKE '%".addslashes(strtolower($kontrak_value))."%'";
			};
			if($kontrak_start!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_start) LIKE '%".addslashes(strtolower($kontrak_start))."%'";
			};
			if($kontrak_p1!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_p1) LIKE '%".addslashes(strtolower($kontrak_p1))."%'";
			};
			if($kontrak_p2!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_p2) LIKE '%".addslashes(strtolower($kontrak_p2))."%'";
			};
			if($kontrak_created_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_created_date) LIKE '%".addslashes(strtolower($kontrak_created_date))."%'";
			};
			if($kontrak_created_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_created_by) LIKE '%".addslashes(strtolower($kontrak_created_by))."%'";
			};
			if($kontrak_updated_date!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_updated_date) LIKE '%".addslashes(strtolower($kontrak_updated_date))."%'";
			};
			if($kontrak_updated_by!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_updated_by) LIKE '%".addslashes(strtolower($kontrak_updated_by))."%'";
			};
			if($kontrak_updated_count!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(kontrak_updated_count) LIKE '%".addslashes(strtolower($kontrak_updated_count))."%'";
			};
			if($m_branch!=''){
				$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
				$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
			};

			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
			$limit = $sql." LIMIT ".$start.",".$end;
			$result = $this->db->query($limit);

			if($nbrows>0){
				foreach($result->result() as $row){
					$arr[] = $row;
				}
				$jsonresult = json_encode($arr);
				return '({"total":"'.$nbrows.'","results":'.$jsonresult.'})';
			} else {
				return '({"total":"0", "results":""})';
			}
		}

		//function for print record
		function t_kontrak_print($kontrak_id,
				 $kontrak_flag,
				 $kontrak_code,
				 $kontrak_type,
				 $kontrak_name,
				 $kontrak_shortname,
				 $kontrak_pm,
				 $kontrak_semcm,
				 $kontrak_value,
				 $kontrak_start,
				 $kontrak_p1,
				 $kontrak_p2,
				 $kontrak_created_date,
				 $kontrak_created_by,
				 $kontrak_updated_date,
				 $kontrak_updated_by,
				 $kontrak_updated_count,
				 $m_branch,$option,$filter){
			//full query
			$sql = "SELECT kontrak_id,
						   kontrak_flag,
						   kontrak_code,
						   kontrak_type,
						   kontrak_name,
						   kontrak_shortname,
						   kontrak_pm,
						   kontrak_semcm,
						   kontrak_value,
						   kontrak_start,
						   kontrak_p1,
						   kontrak_p2,
						   kontrak_created_date,
						   kontrak_created_by,
						   kontrak_updated_date,
						   kontrak_updated_by,
						   kontrak_updated_count,
						   m_branch_id
				    FROM   t_kontrak";

			if($option=='LIST'){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= " (lower(kontrak_id) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_flag) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_code) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_type) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_name) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_shortname) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_pm) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_semcm) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_value) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_start) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_p1) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_p2) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_created_date) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_created_by) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_updated_date) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_updated_by) LIKE '%".addslashes(strtolower($filter))."%' OR lower(kontrak_updated_count) LIKE '%".addslashes(strtolower($filter))."%' OR lower(m_branch_id) LIKE '%".addslashes(strtolower($filter))."%' )";
			} else if($option=='SEARCH'){
				if($kontrak_id!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_id) LIKE '%".addslashes(strtolower($kontrak_id))."%'";
				};
				if($kontrak_flag!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_flag) LIKE '%".addslashes(strtolower($kontrak_flag))."%'";
				};
				if($kontrak_code!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_code) LIKE '%".addslashes(strtolower($kontrak_code))."%'";
				};
				if($kontrak_type!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_type) LIKE '%".addslashes(strtolower($kontrak_type))."%'";
				};
				if($kontrak_name!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_name) LIKE '%".addslashes(strtolower($kontrak_name))."%'";
				};
				if($kontrak_shortname!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_shortname) LIKE '%".addslashes(strtolower($kontrak_shortname))."%'";
				};
				if($kontrak_pm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_pm) LIKE '%".addslashes(strtolower($kontrak_pm))."%'";
				};
				if($kontrak_semcm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_semcm) LIKE '%".addslashes(strtolower($kontrak_semcm))."%'";
				};
				if($kontrak_value!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_value) LIKE '%".addslashes(strtolower($kontrak_value))."%'";
				};
				if($kontrak_start!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_start) LIKE '%".addslashes(strtolower($kontrak_start))."%'";
				};
				if($kontrak_p1!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_p1) LIKE '%".addslashes(strtolower($kontrak_p1))."%'";
				};
				if($kontrak_p2!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_p2) LIKE '%".addslashes(strtolower($kontrak_p2))."%'";
				};
				if($kontrak_created_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_created_date) LIKE '%".addslashes(strtolower($kontrak_created_date))."%'";
				};
				if($kontrak_created_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_created_by) LIKE '%".addslashes(strtolower($kontrak_created_by))."%'";
				};
				if($kontrak_updated_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_updated_date) LIKE '%".addslashes(strtolower($kontrak_updated_date))."%'";
				};
				if($kontrak_updated_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_updated_by) LIKE '%".addslashes(strtolower($kontrak_updated_by))."%'";
				};
				if($kontrak_updated_count!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_updated_count) LIKE '%".addslashes(strtolower($kontrak_updated_count))."%'";
				};
				if($m_branch!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
				};
			}
			$query = $this->db->query($sql);
			return $query->result();
		}

		//function  for export to excel
		function t_kontrak_export_excel($kontrak_id ,$kontrak_flag ,$kontrak_code ,$kontrak_type ,$kontrak_name ,$kontrak_shortname ,$kontrak_pm ,$kontrak_semcm ,$kontrak_value ,$kontrak_start ,$kontrak_p1 ,$kontrak_p2 ,$kontrak_created_date ,$kontrak_created_by ,$kontrak_updated_date ,$kontrak_updated_by ,$kontrak_updated_count ,$m_branch ,$option,$filter){
			//full query
			$sql = "SELECT kontrak_id,
						   kontrak_flag,
						   kontrak_code,
						   kontrak_type,
						   kontrak_name,
						   kontrak_shortname,
						   kontrak_pm,
						   kontrak_semcm,
						   kontrak_value,
						   kontrak_start,
						   kontrak_p1,
						   kontrak_p2,
						   kontrak_created_date,
						   kontrak_created_by,
						   kontrak_updated_date,
						   kontrak_updated_by,
						   kontrak_updated_count,
						   m_branch_id				    FROM   t_kontrak";

			if($option=='LIST'){
				$sql .=preg_match("/WHERE/i",$sql)? " AND ":" WHERE ";
				$sql .= "(lower(kontrak_id) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_flag) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_code) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_type) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_name) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_shortname) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_pm) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_semcm) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_value) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_start) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_p1) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_p2) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_created_date) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_created_by) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_updated_date) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_updated_by) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(kontrak_updated_count) LIKE '%".addslashes(strtolower($filter))."%' OR
						 lower(m_branch_id) LIKE '%".addslashes(strtolower($filter))."%' )";
			} else if($option=='SEARCH'){
				if($kontrak_id!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_id) LIKE '%".addslashes(strtolower($kontrak_id))."%'";
				};
				if($kontrak_flag!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_flag) LIKE '%".addslashes(strtolower($kontrak_flag))."%'";
				};
				if($kontrak_code!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_code) LIKE '%".addslashes(strtolower($kontrak_code))."%'";
				};
				if($kontrak_type!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_type) LIKE '%".addslashes(strtolower($kontrak_type))."%'";
				};
				if($kontrak_name!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_name) LIKE '%".addslashes(strtolower($kontrak_name))."%'";
				};
				if($kontrak_shortname!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_shortname) LIKE '%".addslashes(strtolower($kontrak_shortname))."%'";
				};
				if($kontrak_pm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_pm) LIKE '%".addslashes(strtolower($kontrak_pm))."%'";
				};
				if($kontrak_semcm!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_semcm) LIKE '%".addslashes(strtolower($kontrak_semcm))."%'";
				};
				if($kontrak_value!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_value) LIKE '%".addslashes(strtolower($kontrak_value))."%'";
				};
				if($kontrak_start!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_start) LIKE '%".addslashes(strtolower($kontrak_start))."%'";
				};
				if($kontrak_p1!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_p1) LIKE '%".addslashes(strtolower($kontrak_p1))."%'";
				};
				if($kontrak_p2!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_p2) LIKE '%".addslashes(strtolower($kontrak_p2))."%'";
				};
				if($kontrak_created_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_created_date) LIKE '%".addslashes(strtolower($kontrak_created_date))."%'";
				};
				if($kontrak_created_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_created_by) LIKE '%".addslashes(strtolower($kontrak_created_by))."%'";
				};
				if($kontrak_updated_date!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_updated_date) LIKE '%".addslashes(strtolower($kontrak_updated_date))."%'";
				};
				if($kontrak_updated_by!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_updated_by) LIKE '%".addslashes(strtolower($kontrak_updated_by))."%'";
				};
				if($kontrak_updated_count!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(kontrak_updated_count) LIKE '%".addslashes(strtolower($kontrak_updated_count))."%'";
				};
				if($m_branch!=''){
					$sql.=preg_match("/WHERE/i",$sql)?" AND ":" WHERE ";
					$sql.= " lower(m_branch_id) LIKE '%".addslashes(strtolower($m_branch))."%'";
				};
			}
			$query = $this->db->query($sql);
			return $sql;
		}
		function get_setup($spk_id){
			$kuery = "SELECT 
 	setup_purc.vvalue1 AS nama_purcm,
 	setup_purc.vvalue2 AS jabatan_purcm , setup_kdvo.vvalue1 AS nama_kdvo, setup_kdvo.vvalue2 AS jabatan_kdvo,setup_kcb.vvalue1 AS nama_kcb, setup_kcb.vvalue2 AS jabatan_kcb FROM t_spk
				 
				JOIN t_komp ON t_spk.t_komp_id = t_komp.komp_id
				JOIN t_rapk ON t_komp.m_branch_id = t_rapk.m_branch_id AND t_komp.t_project_id = t_rapk.t_project_id AND t_komp.m_job_id = t_rapk.m_job_id
				JOIN m_setup AS setup_kcb ON setup_kcb.param1 = 'PROCM'
				JOIN m_setup AS setup_purc ON setup_purc.param2 = t_rapk.rapk_procurement
				JOIN m_setup AS setup_kdvo ON setup_kdvo.param1 = 'KDVO'
				WHERE spk_id = " . $spk_id;
			$rs = $this->db->query($kuery);
			if($rs->num_rows()){
				return $rs->row();
			}else{
				return null;
			}
		}
		function get_negodet($spk_id){
			$kuery = "SELECT t_negodet.* FROM `t_negodet`
				JOIN t_spk ON t_negodet.t_nego_id = t_spk.t_nego_id
				WHERE t_spk.spk_id = ". $spk_id ."
				AND t_negodet.negodet_menang = 'Y'
				LIMIT 0,1";
			$query = $this->db->query($kuery);
			return $query->row();
		}
		
		function insert_tpasal($kontrak_type, $kontrak_no){
			$sql = "SELECT pasal_id, pasal_seq, pasal_no, pasal_title, pasal_content, CONCAT(SUBSTRING_INDEX(pasal_content,' ',50),'...') AS pasal_content_partial
					FROM m_pasal
					WHERE pasal_type = '". $kontrak_type ."'
					ORDER BY pasal_seq ASC";
			$result = $this->db->query($sql);
			$nbrows = $result->num_rows();
		
			if($nbrows>0){
				foreach($result->result() as $row){
					$pasal_seq = $row->pasal_seq;
					$pasal_no = $row->pasal_no;
					$pasal_title = $row->pasal_title;
					$pasal_content = $row->pasal_content;
					
					$data_tpasal = array(
						"tpasal_kontrak_no" => $kontrak_no,
						"tpasal_pasal_seq" => $pasal_seq,
						"tpasal_pasal_no" => $pasal_no,
						"tpasal_pasal_title" => $pasal_title,
						"tpasal_pasal_content" => $pasal_content
					);
					$this->db->insert('t_pasal',$data_tpasal);
				}
			}
		}
		
		function get_kontrak_type($div_id){
			$sql = "SELECT m_pasal_type FROM m_div WHERE div_id = '".addslashes($div_id)."'";
			$query = $this->db->query($sql);
			if ($query->num_rows() != 0 ){
				return $query->row('m_pasal_type');
			}else{
				return '';
			}
		}
}
?>